package app.netant.galgame.yuinya.net;

import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.db.sql.manager.DBOfflineManager;
import cn.lily.com.enviroment.Enviroment;
import cn.lily.com.file.excel.io.ExcelWriter;
import cn.lily.com.lang.Basic;

public class YuiNyaNetExcel {
	
	private static DataTable dataTable;
	
	public static void loadData(){
		dataTable = DBOfflineManager.getDBManager().executeQueryTable("select * from ${schema}.LEO_YUINYA_GAL_INFO");
	}
	
	public static void writeExcel(){
		
		ExcelWriter ew = new ExcelWriter("D:/yuinya.xlsx");
		ew.inst();
		
		ew.locateSheet(0);
		
		ew.locateRow(0);
		ew.setCellValue(0,"游戏名字");
		ew.setCellValue(1,"匹配名称");
		ew.setCellValue(2,"YUI地址");
		ew.setCellValue(3,"磁力链接");
		ew.setCellValue(4,"网盘地址");
		ew.setCellValue(5,"网盘文件");
		ew.setCellValue(6,"解压密码");
		
		
		int row = 1;
		while(dataTable.next()){
			
			ew.locateRow(row);
			
			String name = dataTable.getString("GAME_NAME");
			
			String fixName = "";
			String sql = "select * from ${schema}.LEO_GALGAME_INFO_JPS where ( upper(GAME_NAME) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "' or upper(GAME_NAME_PLUS) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "')";
			DataTable newTable = DBOfflineManager.getDBManager().executeQueryTable(sql);
			while(newTable.next()){
				fixName += newTable.getString("GAME_NAME")+",";
				fixName += newTable.getString("GAME_NAME_PLUS")+",";
			}
			
			ew.setCellValue(0,name);
			ew.setCellValue(1,fixName);
			ew.setCellValue(2,dataTable.getString("GAME_HREF"));
			ew.setCellValue(3,dataTable.getString("GAME_THUNDER"));
			ew.setCellValue(4,dataTable.getString("GAME_YUN_URLS"));
			ew.setCellValue(5,dataTable.getString("GAME_FILE_NAM"));
			ew.setCellValue(6,dataTable.getString("GAME_FILE_PWD"));
			
			row++;
			
		}
		
		ew.write();
		
	}
	
	public static void main(String[] args) {
		Enviroment.createEnv();
		Enviroment.getEnv().getLogBean().beginLog();
		loadData();
		writeExcel();
	}
	
}
